package net.sky.oaxz.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import net.sky.oaxz.bean.XzSchool;

@SuppressWarnings("unchecked")
public class XzSchoolDao extends BaseDao{
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	/**
	 * 添加校区
	 * @param 
	 * @return
	 */
	public int addsch(XzSchool xs){
		StringBuffer sql=new StringBuffer("insert into xz_school values (SQ_XZSCHOOL_ID.nextval,?,?,?,to_date(?,'yyyy-mm-dd'),?,?,?,?,?)");
		String [] params=new String[] {xs.getSchName(),xs.getSchDean(),xs.getSchAddress(),xs.getSchBegintime()+"",xs.getSchTel(),
				xs.getSchDesc(),xs.getSchStatus()+"",xs.getSchType()+"",xs.getSchSeq()+""};
		return this.executeSQL(sql.toString(), params);
	}
	
	/**
	 * 显示所有校区
	 * @param schId
	 * @return
	 */
	public List searchSchool(){
		List list =new ArrayList();
		StringBuffer sql=new StringBuffer("select * from xz_school order by sch_seq");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				XzSchool xsc=new XzSchool();
				xsc.setSchId(rs.getInt("sch_id"));
				xsc.setSchName(rs.getString("sch_name"));
				xsc.setSchDean(rs.getString("sch_dean"));
				xsc.setSchAddress(rs.getString("sch_address"));
				xsc.setSchBegintime(rs.getString("sch_begintime"));
				xsc.setSchTel(rs.getString("sch_tel"));
				xsc.setSchDesc(rs.getString("sch_desc"));
				xsc.setSchStatus(rs.getInt("sch_status"));
				xsc.setSchType(rs.getInt("sch_type"));
				xsc.setSchSeq(rs.getInt("sch_seq"));
				list.add(xsc);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**/
	public List getSchool(){
		List list =new ArrayList();
		StringBuffer sql=new StringBuffer("select sch_seq,sch_name2 from xz_school group by sch_seq,sch_name2 order by sch_seq");
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				XzSchool xsc=new XzSchool();
				xsc.setSchName(rs.getString("sch_name2"));
				xsc.setSchSeq(rs.getInt("sch_seq"));
				list.add(xsc);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	/**
	 * 按校区ID查询校区
	 */
	public XzSchool searchSchById(int schId){
		XzSchool sch =null;
		StringBuffer sql=new StringBuffer("select * from xz_school s where s.sch_id = "+schId);
		try{
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				sch=new XzSchool();
				sch.setSchId(rs.getInt("sch_id"));
				sch.setSchName(rs.getString("sch_name"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return sch;
	}
	/**
	 * update 校区信息
	 * @param sch
	 * @return
	 */
     public int updatexz(XzSchool sch){
    	 StringBuffer sql =new StringBuffer("update xz_school s set s.sch_name=?,s.sch_dean=?,s.sch_address=?,s.sch_begintime=to_date(?,'yyyy-mm-dd')," +
				"s.sch_tel=?,s.sch_desc=?,s.sch_status=?,s.sch_type=? where s.sch_id=?");
		String [] params=new String[]{sch.getSchName(),sch.getSchDean(),sch.getSchAddress(),sch.getSchBegintime(),sch.getSchTel(),
				sch.getSchDesc(),sch.getSchStatus()+"",sch.getSchType()+"",sch.getSchId()+""};
		return this.executeSQL(sql.toString(), params);
	}
}
